﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using Top.Api;
using Top.Api.Request;
using Top.Api.Response;
using Top.Api.Domain;
using Top.Api.Util;

using System.Data;
using System.Data.SqlClient;

namespace TopApp.Library
{
    public class KISInterface
    {
        private Database DB;
        public AppDataBase AppDB;

        private int stockId;
        public DataTable inventory;
        public DataTable batchInventory;
        public DataTable item;

        private int billerId;
        private int expItemId;

        public string errMsg;

        public KISInterface()
        {
            this.DB = new Database(new KISDBConfig());
            this.AppDB = new AppDataBase();
            this.stockId = this.getStockId();
            this.billerId = this.getUserId("Manager");
            this.errMsg = "";
            this.initTables();
            this.expItemId = this.getExpItem();
        }

        public bool saveTrade(Trade trade,string expNo)
        {
            this.errMsg = "";
            
            this.initTables(); //为本次操作化数据表数据
            
            int bill_id = this.checkStockBill(trade.Tid.ToString());
            
            if (bill_id == 0)
            {
                
                return this.insertStockBill(trade, expNo);
            }
            else
            {
                
                if (!this.tradeHadExp(trade.Tid))
                {
                    //MessageBox.Show(expNo);
                    return this.updateStockBill(trade, expNo, bill_id);
                }
                else return true;
            }

        }

        // 检查出库单是否已存在,存在则返回其id
        public int checkStockBill( string tradeId )
        {
            this.DB.resetComm();

            this.DB.comm.CommandText = "SELECT FInterID FROM ICStockBill WHERE FBillNo='" + tradeId + "'";
            int bill_id = 0;
            object rs = this.DB.comm.ExecuteScalar();
            if (rs != null) bill_id = Convert.ToInt32(rs);

            return bill_id;
        }

        /**
         * 需要的Trade字段
         * SellerMemo   卖家备注 seller_memo
         * Tid          交易编号 tid
         * Created      交易创建时间 created
         * PayTime      交易付款时间 pay_time
         * Modified     最后更新时间 modified 
         * Orders       交易明细 orders
         */
        public Boolean insertStockBill(Trade trade, string expNo)
        {
            
            // 检查是否有足够的库存以生成出库单
            if (!this.checkInventoryForInsert(trade))
            {
                this.errMsg += "金蝶中商品或赠品的库存量无法满足该订单";
                return false; // 库存不够
            }

            string sellermemo = trade.SellerMemo == null ? "" : trade.SellerMemo;
            Memo memo = new Memo(sellermemo);

            int stockBillType = 21;
            string headTableName = "ICStockBill";
            string bodyTableName = "ICStockBillEntry";
            int bill_id = getNewBillId(headTableName);

            // 开始出库单插入SQL事务
            try
            {
                //插入表头
                this.DB.tComm.CommandText =
                    "INSERT INTO " + headTableName +
                    "(FInterID,FBillNo,FBrNo,FTranType,FCancellation," + // 单据内码,单据编号,公司机构内码,单据类型,是否作废（0未作废1已作废）
                    "FStatus,FUpStockWhenSave,FVchInterID,FROB,FHookStatus," + // 审核状态(0未审核1已审核),更新库存(0审核时1保存时更新库存),凭证内码,红蓝字(1蓝字-1红字),钩稽标志
                    "Fdate,FSupplyID,FSaleStyle,FCheckDate,FCheckerID,FFManagerID," + // 单据日期,供应商ID,销售方式,审核日期,审核人ID,验收人ID
                    "FDeptID,FBillerID,FOrgBillInterID,FSelTranType,FFetchAdd," + // 部门ID,制单人ID,源单内码源单ID,源单类型,交货地点
                    "FExplanation,FManagerID,FEmpID,FSManagerID,FHeadSelfB0122," + // 摘要,主管ID,业务员,保管人ID,自定义字段0122（公司自编订单号）
                    "FMultiCheckLevel1,FHolisticDiscountRate,FMultiCheckDate1,FMultiCheckLevel2,FMultiCheckDate2," + // 一级审核状态,整单折扣率,一级审核日期,二级审核状态,二级审核日期
                    "FHeadSelfB0126,FMultiCheckLevel3,FMultiCheckDate3,FMultiCheckLevel4,FMultiCheckDate4," + // 自定义字段0126（快递单号）,三级审核状态,三级审核日期,四级审核状态,四级审核日期
                    "FMultiCheckLevel5,FMultiCheckDate5,FMultiCheckLevel6,FMultiCheckDate6,FRelateBrID," + // 五级审核状态,五级审核日期,六级审核状态,六级审核日期,分支机构内码
                    "FMarketingStyle,FBrID,FVIPCardID,FVIPScore,FPOSName,FWorkShiftId) " + // 销售业务类型,制单机构,VIP卡ID,VIP积分,POS机名称,班次ID
                    "VALUES " +   // 下面的每一行的记录值都分别对应上面每一行的数据列
                    "(" + bill_id.ToString() + ",'" + trade.Tid.ToString() + "','0'," + stockBillType.ToString() + ",0," + // 2406,'XOUT000100','0',21,0
                    "1,0,0,1,0," + // 0,0,0,1,0
                    "'" + trade.PayTime + "'," + this.getCustomerId("淘宝客") + ",101,'" + DateTime.Now.Date.ToString() + "'," + this.billerId.ToString() + ",0," + // '2013-07-25',347,101,Null,0
                    "0," + this.billerId.ToString() + ",0,0,'快递邮寄'," + // 0,16394,0,0,''
                    "@SellerMemo,0," + this.getEmployeeId( memo.salesman.ToString() ) + ",0,'" + trade.Tid.ToString() + "'," + // '试试试试试试试试试试试试试试试试试试试试试试试试试试试试试试试试试试',0,220,0,'1111111111111111111111111111'
                    "Null,0,Null,Null,Null," + // Null,0,Null,Null,Null
                    "'" + expNo + "',Null,Null,Null,Null," + // '4654879564',Null,Null,Null,Null
                    "Null,Null,Null,Null,0," + // Null,Null,Null,Null,0
                    "12530,0,0,0,'',0)"; // 12530,0,0,0,'',0

                this.DB.tComm.Parameters.Add("@SellerMemo", SqlDbType.NVarChar).Value = sellermemo.Substring(0, sellermemo.Length > 255 ? 255 : sellermemo.Length);
                
                this.DB.tComm.ExecuteNonQuery();

                // 插入表体
                this.DB.tComm.CommandText =
                        "INSERT INTO " + bodyTableName +
                        "(FInterID,FEntryID,FBrNo,FItemID,FMapNumber," + // 单据内码出库单ID(关键字一),分录号（关键字二）,公司机构内码,物料ID,对应代码
                        "FMapName,FDCStockID,FAuxPropID,FBatchNo,FQty," + // 对应名称,调入仓库ID,辅助属性,批号,基本单位实收数量
                        "FUnitID,FAuxQtyMust,Fauxqty,Fauxprice,Famount," + // 计量单位ID,应收数量,实收数量 辅助计量单位的数量,单价 辅助计量单位的单价,金额
                        "Fnote,FSecCoefficient,FKFDate,FSecQty,FKFPeriod," + // 备注,换算率,生产/采购日期,辅助数量,保质期(天)
                        "FAuxPlanPrice,FPlanAmount,FPeriodDate,FConsignPrice,FDiscountRate," + // 辅助计划价,计划价金额,有效期至,代销单价,折扣率
                        "FDiscountAmount,FConsignAmount,FOrgBillEntryID,FSourceBillNo,FSourceTranType," + // 折扣额,代销金额,拆单源单行号,源单单号,源单类型
                        "FSourceInterId,FSourceEntryID,FDVStockID,FOrderBillNo,FDCSPID," + // 源单内码,源单分录,无说明,订单单号,目标仓位
                        "FOrderInterID,FOrderEntryID,FQtyMust,FAuxQtyInvoice,FQtyInvoice," + // 订单内码,订单分录,基本单位应收数量 申请数量（应发数量、应入数量）,开票数量,基本单位开票数量
                        "FSNListID,FContractBillNo,FContractInterID,FContractEntryID,FAllHookQTY," + // 序列号,合同单号,合同内码,合同分录,已钩稽数量
                        "FCurrentHookQTY,FSepcialSaleId) " + // 本期钩稽数量,特价ID
                        "VALUES " +
                        "(" + bill_id.ToString() + ",@FEntryID,'0',@FItemID,''," + //2406,1,'0',1659,''
                        "''," + this.getStockId().ToString() + ",0,@FBatchNo,@FQty," + // '',255,0,'2041',13
                        "@FUnitID,0,@Fauxqty,0,0," + // 69,0,13,0,0
                        "@Fnote,0,@FKFDate,0,@FKFPeriod," + // '',0,'2013-06-18',0,986
                        "0,0,@FPeriodDate,@FConsignPrice,0," + // 0,0,'2016-02-29',800,0
                        "0,@FConsignAmount,0,'',0," + // 0,10400,0,'',0
                        "0,0,0,'',0," + // 0,0,0,'',0
                        "0,0,0,0,0," + // 0,0,0,0,0
                        "0,'',0,0,0," + // 0,'',0,0,0
                        "0,0) "; // 0,0

                this.DB.tComm.Parameters.Clear();
                this.DB.tComm.Parameters.Add("@FEntryID", SqlDbType.Int);  // 分录号
                this.DB.tComm.Parameters.Add("@FItemID", SqlDbType.Int);  // 物料ID
                this.DB.tComm.Parameters.Add("@FBatchNo", SqlDbType.VarChar); // 批号，长度200
                this.DB.tComm.Parameters.Add("@FQty", SqlDbType.Decimal); // 基本单位实收数量
                this.DB.tComm.Parameters.Add("@FUnitID", SqlDbType.Int); // 基本单位ID
                this.DB.tComm.Parameters.Add("@Fauxqty", SqlDbType.Decimal); // 实收数量
                this.DB.tComm.Parameters.Add("@Fnote", SqlDbType.VarChar); //备注，长度255
                this.DB.tComm.Parameters.Add("@FKFDate", SqlDbType.VarChar); //生产/采购日期
                this.DB.tComm.Parameters.Add("@FKFPeriod", SqlDbType.Decimal); // 保质期(天)
                this.DB.tComm.Parameters.Add("@FPeriodDate", SqlDbType.VarChar); //有效期至
                this.DB.tComm.Parameters.Add("@FConsignPrice", SqlDbType.Decimal); //单价
                this.DB.tComm.Parameters.Add("@FConsignAmount", SqlDbType.Decimal); // 小计 单价*数量


                int index_id = 0;
                string item_code = "";
                int item_id = 0;

                // 把商品插入表体
                foreach (Order order in trade.Orders)
                {
                     
                    item_code = order.OuterIid != "" ? order.OuterIid : order.OuterSkuId;
                    item_id = this.getInterIdByCode(item_code);

                    DataTable order_rows = this.getOrderRows(item_id, Convert.ToInt32(order.Num));

                    foreach (DataRow row in order_rows.Rows)
                    {
                        index_id++;

                        this.DB.tComm.Parameters["@FEntryID"].Value = index_id;
                        this.DB.tComm.Parameters["@FItemID"].Value = item_id;
                        this.DB.tComm.Parameters["@FBatchNo"].Value = row["FBatchNo"];
                        this.DB.tComm.Parameters["@FQty"].Value = row["FQty"];
                        this.DB.tComm.Parameters["@FUnitID"].Value = this.getUnitId(item_id);
                        this.DB.tComm.Parameters["@Fauxqty"].Value = row["FQty"];
                        this.DB.tComm.Parameters["@Fnote"].Value = order.Title.Substring(0, order.Title.Length > 255 ? 255 : order.Title.Length);

                        Boolean isPeriod = false;
                        if (row["FKFDate"].ToString().Trim() != "") isPeriod = true;
                        
                        this.DB.tComm.Parameters["@FKFDate"].Value = isPeriod ? row["FKFDate"] : DBNull.Value;
                        this.DB.tComm.Parameters["@FKFPeriod"].Value = isPeriod ? row["FKFPeriod"] : 0; 
                        this.DB.tComm.Parameters["@FPeriodDate"].Value = isPeriod ? row["FPeriodDate"] : DBNull.Value;

                        this.DB.tComm.Parameters["@FConsignPrice"].Value = Convert.ToDecimal(order.TotalFee) / Convert.ToDecimal(order.Num);
                        this.DB.tComm.Parameters["@FConsignAmount"].Value = Convert.ToDecimal(order.TotalFee);
                        
                        this.DB.tComm.ExecuteNonQuery();
                    }

                }

                // 把赠品插入表体
                foreach(Memo.Lagniappe lagniappe in memo.lagniappes)
                {
                    
                    item_code = lagniappe.code;
                    item_id = this.getInterIdByCode(item_code);

                    DataTable order_rows = this.getOrderRows(this.getInterIdByCode(item_code), lagniappe.quantity);

                    foreach (DataRow row in order_rows.Rows)
                    {
                        index_id++;

                        this.DB.tComm.Parameters["@FEntryID"].Value = index_id;
                        this.DB.tComm.Parameters["@FItemID"].Value = item_id;
                        this.DB.tComm.Parameters["@FBatchNo"].Value = row["FBatchNo"];
                        this.DB.tComm.Parameters["@FQty"].Value = row["FQty"];
                        this.DB.tComm.Parameters["@FUnitID"].Value = this.getUnitId(item_id);
                        this.DB.tComm.Parameters["@Fauxqty"].Value = row["FQty"];
                        this.DB.tComm.Parameters["@Fnote"].Value = "赠品";

                        Boolean isPeriod = false;
                        if (row["FKFDate"].ToString().Trim() != "") isPeriod = true;
                        
                        this.DB.tComm.Parameters["@FKFDate"].Value = isPeriod ? row["FKFDate"] : DBNull.Value;
                        this.DB.tComm.Parameters["@FKFPeriod"].Value = isPeriod ? row["FKFPeriod"] : 0; 
                        this.DB.tComm.Parameters["@FPeriodDate"].Value = isPeriod ? row["FPeriodDate"] : DBNull.Value;

                        this.DB.tComm.Parameters["@FConsignPrice"].Value = 0;
                        this.DB.tComm.Parameters["@FConsignAmount"].Value = 0;

                        this.DB.tComm.ExecuteNonQuery();
                    }
                }

                // 插入运费
                this.DB.tComm.Parameters["@FEntryID"].Value = ++index_id;
                this.DB.tComm.Parameters["@FItemID"].Value = this.expItemId;
                this.DB.tComm.Parameters["@FBatchNo"].Value = "";
                this.DB.tComm.Parameters["@FQty"].Value = 1;
                this.DB.tComm.Parameters["@FUnitID"].Value = this.getUnitId(this.expItemId);
                this.DB.tComm.Parameters["@Fauxqty"].Value = 1;
                this.DB.tComm.Parameters["@Fnote"].Value = "运费"; 
                this.DB.tComm.Parameters["@FKFDate"].Value = DBNull.Value;
                this.DB.tComm.Parameters["@FKFPeriod"].Value = 0; 
                this.DB.tComm.Parameters["@FPeriodDate"].Value = DBNull.Value;
                this.DB.tComm.Parameters["@FConsignPrice"].Value = Convert.ToDecimal(trade.PostFee);
                this.DB.tComm.Parameters["@FConsignAmount"].Value = Convert.ToDecimal(trade.PostFee);
                this.DB.tComm.ExecuteNonQuery();

                this.DB.tComm.CommandText = "CheckInventory";
                this.DB.tComm.CommandType = CommandType.StoredProcedure;
                this.DB.tComm.Parameters.Clear();

                this.DB.tComm.ExecuteNonQuery();

                this.DB.commit();

            }
            catch (Exception ex)
            {
                this.DB.rollBack();
                this.errMsg += "执行插入数据记录事务时出错。异常信息："+ex.Message;
                return false;
            }
            finally
            {
                this.DB.resetTComm();
            }

            return true;

        }

        public Boolean updateStockBill(Trade trade, string expNo, int bill_id)
        {

            // 检查是否有足够的库存以更新出库单
            if (!this.checkInventoryForUpdate(trade, bill_id))
            {
                this.errMsg += "金蝶中商品或赠品的库存量无法满足该订单";
                return false; // 库存不够
            }

            string sellermemo = trade.SellerMemo == null ? "" : trade.SellerMemo;
            Memo memo = new Memo(sellermemo);

            string headTableName = "ICStockBill";
            string bodyTableName = "ICStockBillEntry";

            this.DB.resetTComm();

            // 开始出库单更新SQL事务
            try
            {
                //更新表头
                this.DB.tComm.CommandText =
                    "UPDATE " + headTableName + " SET " +
                    "FCheckDate='" + DateTime.Now.Date.ToString() + "'," + // 最后更新时间（金蝶出库单的审核时间字段）
                    "FExplanation=@SellerMemo," + // 摘要
                    "FEmpID=" + this.getEmployeeId(memo.salesman.ToString()) + "," + // 业务员
                    "FHeadSelfB0126='" + expNo + "' " + // 快递单号
                    "WHERE FInterID=" + bill_id.ToString();

                this.DB.tComm.Parameters.Add("@SellerMemo", SqlDbType.NVarChar).Value = sellermemo.Substring(0, sellermemo.Length > 255 ? 255 : sellermemo.Length);
                
                this.DB.tComm.ExecuteNonQuery();

                // 删除所有表体
                this.DB.tComm.CommandText = "DELETE FROM " + bodyTableName + " WHERE FInterID="+bill_id.ToString();
                this.DB.tComm.Parameters.Clear();
                this.DB.tComm.ExecuteNonQuery();

                // 重新插入表体
                this.DB.tComm.CommandText =
                        "INSERT INTO " + bodyTableName +
                        "(FInterID,FEntryID,FBrNo,FItemID,FMapNumber," + // 单据内码出库单ID(关键字一),分录号（关键字二）,公司机构内码,物料ID,对应代码
                        "FMapName,FDCStockID,FAuxPropID,FBatchNo,FQty," + // 对应名称,调入仓库ID,辅助属性,批号,基本单位实收数量
                        "FUnitID,FAuxQtyMust,Fauxqty,Fauxprice,Famount," + // 计量单位ID,应收数量,实收数量 辅助计量单位的数量,单价 辅助计量单位的单价,金额
                        "Fnote,FSecCoefficient,FKFDate,FSecQty,FKFPeriod," + // 备注,换算率,生产/采购日期,辅助数量,保质期(天)
                        "FAuxPlanPrice,FPlanAmount,FPeriodDate,FConsignPrice,FDiscountRate," + // 辅助计划价,计划价金额,有效期至,代销单价,折扣率
                        "FDiscountAmount,FConsignAmount,FOrgBillEntryID,FSourceBillNo,FSourceTranType," + // 折扣额,代销金额,拆单源单行号,源单单号,源单类型
                        "FSourceInterId,FSourceEntryID,FDVStockID,FOrderBillNo,FDCSPID," + // 源单内码,源单分录,无说明,订单单号,目标仓位
                        "FOrderInterID,FOrderEntryID,FQtyMust,FAuxQtyInvoice,FQtyInvoice," + // 订单内码,订单分录,基本单位应收数量 申请数量（应发数量、应入数量）,开票数量,基本单位开票数量
                        "FSNListID,FContractBillNo,FContractInterID,FContractEntryID,FAllHookQTY," + // 序列号,合同单号,合同内码,合同分录,已钩稽数量
                        "FCurrentHookQTY,FSepcialSaleId) " + // 本期钩稽数量,特价ID
                        "VALUES " +
                        "(" + bill_id.ToString() + ",@FEntryID,'0',@FItemID,''," + //2406,1,'0',1659,''
                        "''," + this.getStockId().ToString() + ",0,@FBatchNo,@FQty," + // '',255,0,'2041',13
                        "@FUnitID,0,@Fauxqty,0,0," + // 69,0,13,0,0
                        "@Fnote,0,@FKFDate,0,@FKFPeriod," + // '',0,'2013-06-18',0,986
                        "0,0,@FPeriodDate,@FConsignPrice,0," + // 0,0,'2016-02-29',800,0
                        "0,@FConsignAmount,0,'',0," + // 0,10400,0,'',0
                        "0,0,0,'',0," + // 0,0,0,'',0
                        "0,0,0,0,0," + // 0,0,0,0,0
                        "0,'',0,0,0," + // 0,'',0,0,0
                        "0,0) "; // 0,0

                this.DB.tComm.Parameters.Clear();
                this.DB.tComm.Parameters.Add("@FEntryID", SqlDbType.Int);  // 分录号
                this.DB.tComm.Parameters.Add("@FItemID", SqlDbType.Int);  // 物料ID
                this.DB.tComm.Parameters.Add("@FBatchNo", SqlDbType.VarChar); // 批号，长度200
                this.DB.tComm.Parameters.Add("@FQty", SqlDbType.Decimal); // 基本单位实收数量
                this.DB.tComm.Parameters.Add("@FUnitID", SqlDbType.Int); // 基本单位ID
                this.DB.tComm.Parameters.Add("@Fauxqty", SqlDbType.Decimal); // 实收数量
                this.DB.tComm.Parameters.Add("@Fnote", SqlDbType.VarChar); //备注，长度255
                this.DB.tComm.Parameters.Add("@FKFDate", SqlDbType.VarChar); //生产/采购日期
                this.DB.tComm.Parameters.Add("@FKFPeriod", SqlDbType.Decimal); // 保质期(天)
                this.DB.tComm.Parameters.Add("@FPeriodDate", SqlDbType.VarChar); //有效期至
                this.DB.tComm.Parameters.Add("@FConsignPrice", SqlDbType.Decimal); //单价
                this.DB.tComm.Parameters.Add("@FConsignAmount", SqlDbType.Decimal); // 小计 单价*数量


                int index_id = 0;
                string item_code = "";
                int item_id = 0;

                // 把商品插入表体
                foreach (Order order in trade.Orders)
                {
                    
                    item_code = order.OuterIid != "" ? order.OuterIid : order.OuterSkuId;
                    item_id = this.getInterIdByCode(item_code);

                    DataTable order_rows = this.getOrderRows(item_id, Convert.ToInt32(order.Num));

                    foreach (DataRow row in order_rows.Rows)
                    {
                        index_id++;

                        this.DB.tComm.Parameters["@FEntryID"].Value = index_id;
                        this.DB.tComm.Parameters["@FItemID"].Value = item_id;
                        this.DB.tComm.Parameters["@FBatchNo"].Value = row["FBatchNo"];
                        this.DB.tComm.Parameters["@FQty"].Value = row["FQty"]; 
                        this.DB.tComm.Parameters["@FUnitID"].Value = this.getUnitId(item_id);
                        this.DB.tComm.Parameters["@Fauxqty"].Value = row["FQty"];
                        this.DB.tComm.Parameters["@Fnote"].Value = order.Title.Substring(0, order.Title.Length > 255 ? 255 : order.Title.Length);

                        Boolean isPeriod = false;
                        if (row["FKFDate"].ToString().Trim() != "") isPeriod = true;
                        
                        this.DB.tComm.Parameters["@FKFDate"].Value = isPeriod ? row["FKFDate"] : DBNull.Value;
                        this.DB.tComm.Parameters["@FKFPeriod"].Value = isPeriod ? row["FKFPeriod"] : 0; 
                        this.DB.tComm.Parameters["@FPeriodDate"].Value = isPeriod ? row["FPeriodDate"] : DBNull.Value;

                        this.DB.tComm.Parameters["@FConsignPrice"].Value = Convert.ToDecimal(order.TotalFee);
                        this.DB.tComm.Parameters["@FConsignAmount"].Value = Convert.ToDecimal(order.TotalFee) * Convert.ToInt32(this.DB.tComm.Parameters["@FQty"].Value);
                        
                        this.DB.tComm.ExecuteNonQuery();
                    }

                }

                // 把赠品插入表体
                foreach (Memo.Lagniappe lagniappe in memo.lagniappes)
                {
                    item_code = lagniappe.code;
                    item_id = this.getInterIdByCode(item_code);

                    DataTable order_rows = this.getOrderRows(this.getInterIdByCode(item_code), lagniappe.quantity);

                    foreach (DataRow row in order_rows.Rows)
                    {
                        index_id++;

                        this.DB.tComm.Parameters["@FEntryID"].Value = index_id;
                        this.DB.tComm.Parameters["@FItemID"].Value = item_id;
                        this.DB.tComm.Parameters["@FBatchNo"].Value = row["FBatchNo"];
                        this.DB.tComm.Parameters["@FQty"].Value = row["FQty"];
                        this.DB.tComm.Parameters["@FUnitID"].Value = this.getUnitId(item_id);
                        this.DB.tComm.Parameters["@Fauxqty"].Value = row["FQty"];
                        this.DB.tComm.Parameters["@Fnote"].Value = "赠品";
                        
                        Boolean isPeriod = false;
                        if (row["FKFDate"].ToString().Trim() != "") isPeriod = true;
                        
                        this.DB.tComm.Parameters["@FKFDate"].Value = isPeriod ? row["FKFDate"] : DBNull.Value;
                        this.DB.tComm.Parameters["@FKFPeriod"].Value = isPeriod ? row["FKFPeriod"] : 0; 
                        this.DB.tComm.Parameters["@FPeriodDate"].Value = isPeriod ? row["FPeriodDate"] : DBNull.Value;

                        this.DB.tComm.Parameters["@FConsignPrice"].Value = 0;
                        this.DB.tComm.Parameters["@FConsignAmount"].Value = 0;

                        this.DB.tComm.ExecuteNonQuery();
                    }
                }

                // 插入运费
                this.DB.tComm.Parameters["@FEntryID"].Value = ++index_id;
                this.DB.tComm.Parameters["@FItemID"].Value = this.expItemId;
                this.DB.tComm.Parameters["@FBatchNo"].Value = "";
                this.DB.tComm.Parameters["@FQty"].Value = 1;
                this.DB.tComm.Parameters["@FUnitID"].Value = this.getUnitId(this.expItemId);
                this.DB.tComm.Parameters["@Fauxqty"].Value = 1;
                this.DB.tComm.Parameters["@Fnote"].Value = "运费"; 
                this.DB.tComm.Parameters["@FKFDate"].Value = DBNull.Value;
                this.DB.tComm.Parameters["@FKFPeriod"].Value = 0; 
                this.DB.tComm.Parameters["@FPeriodDate"].Value = DBNull.Value;
                this.DB.tComm.Parameters["@FConsignPrice"].Value = Convert.ToDecimal(trade.PostFee);
                this.DB.tComm.Parameters["@FConsignAmount"].Value = Convert.ToDecimal(trade.PostFee);
                this.DB.tComm.ExecuteNonQuery();

                this.DB.tComm.CommandText = "CheckInventory";
                this.DB.tComm.CommandType = CommandType.StoredProcedure;
                this.DB.tComm.Parameters.Clear();

                this.DB.tComm.ExecuteNonQuery();

                this.DB.commit();

            }
            catch (Exception ex)
            {
                this.DB.rollBack();
                this.errMsg += "更新已有出库单时，执行插入数据记录事务时出错。异常信息：" + ex.Message;
                return false;
            }

            return true;
        }

        public Boolean deleteStockBill(Trade trade)
        {
            int bill_id = this.checkStockBill(trade.Tid.ToString());

            // 反审核
            this.unCheckedABill(bill_id);

            string headTableName = "ICStockBill";
            string bodyTableName = "ICStockBillEntry";

            this.DB.resetTComm();

            try
            {

                this.DB.tComm.CommandText = "DELETE FROM " + headTableName + " WHERE FBillNo='" + trade.Tid.ToString() + "'";
                this.DB.tComm.ExecuteNonQuery();

                this.DB.tComm.CommandText = "DELETE FROM " + bodyTableName + " WHERE FInterID=" + bill_id.ToString();
                this.DB.tComm.ExecuteNonQuery();

                this.DB.tComm.CommandText = "CheckInventory";
                this.DB.tComm.CommandType = CommandType.StoredProcedure;
                this.DB.tComm.Parameters.Clear();

                this.DB.tComm.ExecuteNonQuery();

                this.DB.commit();

            }
            catch (Exception ex)
            {
                this.DB.rollBack();
                this.errMsg += "执行删除数据记录事务时出错。异常信息：" + ex.Message;
                return false;
            }
            finally
            {
                this.DB.resetTComm();
            }

            return true;
        }

        // 反审核一个出库单
        public Boolean unCheckedABill(int bill_id)
        {
            this.DB.resetComm();

            this.DB.comm.CommandText = "UPDATE ICStockBill SET FStatus=0,FCheckDate=Null,FCheckerID=0 WHERE FInterID=" + bill_id.ToString();

            if (this.DB.comm.ExecuteNonQuery() > 0) return true;
            else return false;
        }

        public Boolean tradeHadExp(long tid)
        {
            this.DB.resetComm();

            this.DB.comm.CommandText = "SELECT FHeadSelfB0126 FROM ICStockBill WHERE FBillNo='" + tid.ToString() + "'";
            
            object rs = this.DB.comm.ExecuteScalar();
            //MessageBox.Show(Convert.ToString(rs));
            if (rs == null || rs == DBNull.Value || Convert.ToString(rs).Trim() == "") return false;
            else return true;
        }

        // 为新的出库单获取一个最新的唯一内码
        public int getNewBillId(string TableName)
        {
            this.DB.comm.CommandText = "GetICMaxNum";
            this.DB.comm.CommandType = CommandType.StoredProcedure;
            this.DB.comm.Parameters.Add("@TableName", SqlDbType.VarChar).Direction = ParameterDirection.Input;
            this.DB.comm.Parameters.Add("@FInterID", SqlDbType.Int).Direction = ParameterDirection.Output;

            this.DB.comm.Parameters["@TableName"].Value = TableName;

            this.DB.comm.ExecuteNonQuery();

            int new_id = (int)this.DB.comm.Parameters["@FInterID"].Value;

            this.DB.resetComm();

            return new_id;
        }

        // 为插入新的出库单检查库存
        public Boolean checkInventoryForInsert(Trade trade)
        {
            // 检查货品的库存
            foreach (Order order in trade.Orders)
            {
                string code = order.OuterIid != "" ? order.OuterIid : order.OuterSkuId;
                int inter_id = this.getInterIdByCode(code);

                if (inter_id == 0)
                {
                    this.errMsg += "没有找到货号" + code + "所对应的货品；";
                    this.AppDB.insertFailItem(order.NumIid.ToString(), code);
                    return false; // 没有找到货号所对应的货品
                }

                DataRow[] rows = this.inventory.Select("FItemID=" + inter_id.ToString());
                if (rows.Length == 0 || Convert.ToInt64(rows[0]["Qty"]) < order.Num)
                {
                    string msg = "货号" + code + "所对应的货品没有库存；";
                    this.errMsg += msg;
                    this.AppDB.insertFailInventory(code, msg);
                    return false; // 没有库存
                }
            }

            // 检查赠品的库存
            Memo memo = new Memo(trade.SellerMemo);
            foreach (Memo.Lagniappe lagniappe in memo.lagniappes)
            {
                int inter_id = this.getInterIdByCode(lagniappe.code);

                if (inter_id == 0)
                {
                    this.errMsg += "没有找到赠品" + lagniappe.code + "所对应的货品；";
                    this.AppDB.insertFailItem("0", lagniappe.code);
                    return false; // 没有找到货号所对应的货品
                }

                DataRow[] rows = this.inventory.Select("FItemID=" + inter_id.ToString());
                if (rows.Length == 0 || Convert.ToInt32(rows[0]["Qty"]) < lagniappe.quantity)
                {
                    string msg = "赠品" + lagniappe.code + "所对应的货品没有库存；";
                    this.errMsg += msg;
                    this.AppDB.insertFailInventory(lagniappe.code, msg);
                    return false; // 没有库存
                }
            }

            return true;
        }

        // 为插入新的出库单检查库存(由checkInventoryForInsert改动而来)
        public Boolean checkInventoryForUpdate(Trade trade,int bill_id)
        {
            DataTable billTable = this.getBillEntrys(bill_id);

            // 检查货品的库存
            foreach (Order order in trade.Orders)
            {
                string code = order.OuterIid != "" ? order.OuterIid : order.OuterSkuId;
                int inter_id = this.getInterIdByCode(code);

                if (inter_id == 0)
                {
                    this.errMsg += "没有找到货号" + code + "所对应的货品；";
                    this.AppDB.insertFailItem(order.NumIid.ToString(), code);
                    return false; // 没有找到货号所对应的货品
                }

                DataRow[] rows = this.inventory.Select("FItemID=" + inter_id.ToString());
                
                DataRow[] billRows = billTable.Select("FItemID=" + inter_id.ToString());
                int bill_existed = 0;
                if (billRows.Length > 0)
                {
                    bill_existed = Convert.ToInt32(billRows[0]["Qty"]);
                }

                if (rows.Length == 0 || Convert.ToInt64(rows[0]["Qty"]) < (order.Num - bill_existed))
                {
                    string msg = "货号" + code + "所对应的货品没有库存；";
                    this.errMsg += msg;
                    this.AppDB.insertFailInventory(code, msg);
                    return false; // 没有库存
                }
            }

            // 检查赠品的库存
            Memo memo = new Memo(trade.SellerMemo);
            foreach (Memo.Lagniappe lagniappe in memo.lagniappes)
            {
                int inter_id = this.getInterIdByCode(lagniappe.code);

                if (inter_id == 0)
                {
                    this.errMsg += "没有找到赠品" + lagniappe.code + "所对应的货品；";
                    this.AppDB.insertFailItem("0", lagniappe.code);
                    return false; // 没有找到货号所对应的货品
                }

                DataRow[] rows = this.inventory.Select("FItemID=" + inter_id.ToString());

                DataRow[] billRows = billTable.Select("FItemID=" + inter_id.ToString());
                int bill_existed = 0;
                if (billRows.Length > 0)
                {
                    bill_existed = Convert.ToInt32(billRows[0]["Qty"]);
                }

                if (rows.Length == 0 || Convert.ToInt32(rows[0]["Qty"]) < (lagniappe.quantity - bill_existed))
                {
                    string msg = "赠品" + lagniappe.code + "所对应的货品没有库存；";
                    this.errMsg += msg;
                    this.AppDB.insertFailInventory(lagniappe.code, msg);
                    return false; // 没有库存
                }
            }

            return true;
        }

        // 获取出库单的货品库存表（不包含批号，同一货品的不同批号记录合并为一条）
        public DataTable getBillEntrys(int bill_id)
        {
            this.DB.resetComm();
            this.DB.comm.CommandText = "SELECT b.FItemID, i.FNumber, SUM(b.FQty) AS Qty " +
                "FROM ICStockBillEntry b LEFT OUTER JOIN " +
                "t_Item i ON i.FItemID = b.FItemID " +
                "WHERE (b.FInterID = 1225) " +
                "GROUP BY b.FItemID, i.FNumber";

            SqlDataAdapter adpt = new SqlDataAdapter(this.DB.comm);
            DataTable table = new DataTable();
            adpt.Fill(table);

            return table;
        }

        // 为正在生成的出库单中的一个货品按具体的金蝶库存情况生成分录项
        public DataTable getOrderRows(int item_id,int req)
        {
            //MessageBox.Show("需求： "+item_id + ":" + req);
            DataTable table = this.batchInventory.Clone();

            DataRow[] rows = this.batchInventory.Select("FItemID=" + item_id.ToString() + " AND FQty>0", "days ASC");

            foreach (DataRow row in rows)
            {
                /*
                string s = "";
                foreach (object d in row.ItemArray)
                {
                    s += "【"+d.ToString()+"】";
                }*/
                //MessageBox.Show(s);

                if (Convert.ToInt32(row["FQty"]) >= req)
                {
                    table.Rows.Add(new Object[] { row["FItemID"], row["FBatchNo"], req, row["FKFDate"], row["FKFPeriod"], row["FPeriodDate"], row["days"] });
                    req = 0;
                }
                else
                {
                    table.Rows.Add(new Object[] { row["FItemID"], row["FBatchNo"], row["FQty"], row["FKFDate"], row["FKFPeriod"], row["FPeriodDate"], row["days"] });
                    req = req - Convert.ToInt32(row["FQty"]);
                }

                if (req <= 0) break;
            }

            return table;
        }

        public int getInterIdByCode(string code)
        {
            DataRow[] rows = this.item.Select("FNumber='"+code+"'");
            if (rows.Length == 0) return 0;
            else return Convert.ToInt32( rows[0]["FItemID"]);
        }

        private int getStockId()
        {
            this.DB.resetComm();
            this.DB.comm.CommandText = "SELECT FItemID, FName " +
                "FROM t_Stock " +
                "WHERE (FName = '" + Properties.Settings.Default.kis_taobaoStockName + "')";

            int id = (int)this.DB.comm.ExecuteScalar();

            if (id > 0)
            {
                return id;
            }
            else
            {
                throw new Exception("错误的仓库名称" + Properties.Settings.Default.kis_taobaoStockName);
            }
        }

        private int getUserId( string user_name )
        {
            this.DB.resetComm();
            this.DB.comm.CommandText = "SELECT FUserID " +
                "FROM t_User " +
                "WHERE (FName = '" + user_name + "')";

            int id = Convert.ToInt32(this.DB.comm.ExecuteScalar());

            return id;
        }

        public string getUserName(int user_id)
        {
            this.DB.resetComm();
            this.DB.comm.CommandText = "SELECT FName " +
                "FROM t_User " +
                "WHERE (FUserID = " + user_id + ")";

            string name = Convert.ToString(this.DB.comm.ExecuteScalar());

            return name;
        }

        public int getCustomerId(string name)
        {
            this.DB.resetComm();
            this.DB.comm.CommandText = "SELECT FItemID " +
                "FROM t_Organization " +
                "WHERE (FName = '" + name + "')";

            int id = Convert.ToInt32(this.DB.comm.ExecuteScalar());

            return id;
        }

        public string getCustomerName(int id)
        {
            this.DB.resetComm();
            this.DB.comm.CommandText = "SELECT FName " +
                "FROM t_Organization " +
                "WHERE (FItemID = " + id + ")";

            string name = Convert.ToString(this.DB.comm.ExecuteScalar());

            return name;
        }

        public int getEmployeeId(string name)
        {
            this.DB.resetComm();
            this.DB.comm.CommandText = "SELECT FItemID " +
             "FROM t_Emp " +
             "WHERE (FName = '" + name + "')";

            int id = Convert.ToInt32(this.DB.comm.ExecuteScalar());

            return id; 
        }

        public string getEmployeeName(int id)
        {

            this.DB.comm.CommandText = "SELECT FName " +
                "FROM t_Emp " +
                "WHERE (FItemID = " + id + ")";

            string name = Convert.ToString(this.DB.comm.ExecuteScalar());

            this.DB.resetComm();

            return name;
        }

        // 获取代表运费的货品
        public int getExpItem()
        {
            string keyword = "运费";

            DataRow[] rows = this.item.Select("FName LIKE '%" + keyword + "%'");

            if (rows.Length == 0) return 0;
            else
            {
                // 设置库存，以确保永远都有足够的库存
                int item_id = Convert.ToInt32(rows[0]["FItemID"]);
                setExpInventory(item_id);

                return item_id;
            }
        }

        // 设置淘宝仓的运费库存为999999
        public void setExpInventory(int item_id)
        {
            this.DB.resetTComm();

            try
            {
                this.DB.tComm.CommandText = "DELETE FROM ICInventory WHERE FItemID=" + item_id.ToString();
                this.DB.tComm.ExecuteNonQuery();

                this.DB.tComm.CommandText = "INSERT INTO ICInventory (FBrNo,FItemID,FBatchNo,FStockID,FQty,FBal,FStockPlaceID,FKFPeriod,FKFDate,FQtyLock,FAuxPropID,FSecQty) " +
                    "VALUES (0," + item_id.ToString() + ",''," + this.stockId.ToString() + ",999999,0,0,0,'',0,0,0)";
                this.DB.tComm.ExecuteNonQuery();

                this.DB.commit();
            }
            catch
            {
                this.DB.rollBack();
            }
            finally
            {
                this.DB.resetTComm();
            }
        }

        // 获取物料的计量单位ID
        public int getUnitId( int item_id )
        {
            DataRow[] rows = this.item.Select("FItemID="+item_id.ToString());
            if (rows.Length > 0)
            {
                return Convert.ToInt32(rows[0]["FUnitID"]);
            }
            else
            {
                return 0;
            }
        }

        private void initTables()
        {
            this.inventory = new DataTable();
            this.batchInventory = new DataTable();
            this.item = new DataTable();

            this.DB.resetComm();

            this.DB.comm.CommandText =
                "SELECT FItemID, SUM(FQty) as Qty " +
                "FROM ICInventory " +
                "WHERE (FStockID = " + this.stockId.ToString() + ") " +
                "GROUP BY FItemID";

            SqlDataAdapter adpt = new SqlDataAdapter(this.DB.comm);
            adpt.Fill(this.inventory);

            this.DB.comm.CommandText =
                "SELECT FItemID,FBatchNo,FQty,FKFDate, FKFPeriod, DATEADD([day], FKFPeriod,FKFDate) AS FPeriodDate, DATEDIFF([day], GETDATE(), DATEADD([day], FKFPeriod, FKFDate)) AS days " +
                "FROM ICInventory " +
                "WHERE (FStockID = " + this.stockId.ToString() + ") " +
                "GROUP BY FItemID, FBatchNo, FQty, FKFDate, FKFPeriod";

            adpt.Fill(this.batchInventory);

            this.DB.comm.CommandText =
                "SELECT * " +
                "FROM t_ICItem";

            adpt.Fill(this.item);

        }
    }
}
